﻿using STHPMS1.DAL.D_Core;
using System.Data;
using ServiceStack.OrmLite;
using STHPMS1.ICore.I_Entity.Stmpms1;
using STHPMS1.Common.C_Entity.Stmpms1;
using STHPMS1.ICore.I_DAL.Repository;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace STHPMS1.DAL.D_Repository
{
    public class B_UnitRepository : DalBase, IB_UnitRepository
    {
        public List<string> GetUnits(string segId)
        {
            List<string> units = new List<string>(); 
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                conn.Open();
                string sql = string.Format(@"select UNIT from B_INVNTORY_UNIT where SEGID='{0}' order by UNIT", segId);
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
               
                da.Fill(dt);
                da.FillSchema(dt, SchemaType.Source);

            }
            foreach(DataRow dr in dt.Rows)
            {
                units.Add(dr["unit"].ToString());
            }
            return units;
        }

        //public double GetMinBeginMP(string inspecid, string rdwayid, int d)
        //{
        //    using (IDbConnection conn = Adapter.OpenDbConnection())
        //    {
        //        return conn.Scalar<int>("select MIN(BEGINMP) as BEGINMP from B_UNIT where UNIT in(select UNIT from  E_ROADWAYDETAIL where INSPECID='" + inspecid + "' and RDWAYID='" + rdwayid + "' and DIRECTION_DICTID="+d+")");
        //    }
        //}
        //public double GetMaxEndMP(string inspecid, string rdwayid, int d)
        //{
        //    using (IDbConnection conn = Adapter.OpenDbConnection())
        //    {
        //        return conn.Scalar<int>("select MAX(ENDMP) as ENDMP from B_UNIT where UNIT in(select UNIT from  E_ROADWAYDETAIL where INSPECID='" + inspecid + "' and RDWAYID='" + rdwayid + "' and DIRECTION_DICTID=" + d + ")");
        //    }
        //}
        public List<T> GetUnitList<T>(string dir, string rdid) where T:class,IB_Unit
        {
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                string sql = string.Format(@"select * from B_UNIT where UNIT in
(select UNIT from B_INVNTORY_UNIT
where SEGID in(select SEGID 
from B_INVNTORY where RDWAYID='{0}' and DIRECTION_DICTID={1}))", rdid, dir);
                return conn.Select<T>(sql);
            }
        }


        /// <summary>
        /// 根据路段编号返回单元和评定数据
        /// </summary>
        /// <param name="ldID"></param>
        /// <returns></returns>
        public DataTable GetUnitZB(string ldID)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                string sql = string.Format(@"select b.UNIT, b.BEGINMP,b.ENDMP,e.MQI,e.PCI,e.PQI,e.SRI,e.PSSI,e.RQI 
from B_UNIT b inner join E_ROADWAYDETAIL e on b.UNIT = e.UNIT
where b.UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='{0}')
order by b.BEGINMP", ldID);
               using(SqlDataAdapter da = new SqlDataAdapter(sql,conn))
               {
                   da.Fill(dt);
                   da.FillSchema(dt, SchemaType.Source);
                   return dt;
               }
            }
        }

        /// <summary>
        /// 根据养护路段编号返回该路段的平均指标
        /// </summary>
        /// <param name="segId"></param>
        /// <returns></returns>
        public DataRow GetUnitAvgZB(string segId)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                string sql = string.Format(@"select avg(e.MQI)mqi,avg(e.PCI)pci,avg(e.PQI)pqi,avg(e.SRI)sri,avg(e.PSSI)pssi,avg(e.RQI) rqi from  E_ROADWAYDETAIL e 
where e.UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='{0}')", segId);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    da.Fill(dt);
                    da.FillSchema(dt, SchemaType.Source);
                    if(dt.Rows.Count > 0)
                    {
                        return dt.Rows[0];
                    }
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据路线编号返回单元和当前年最新的评定数据
        /// </summary>
        /// <param name="ldID"></param>
        /// <returns></returns>
        public DataTable GetUnitZBByRdid(string rdid,int dir)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
//                string sql = string.Format(@"select  b.BEGINMP,b.ENDMP,e.PCI,e.PQI,e.SRI,e.PSSI,e.RQI 
//from B_UNIT b inner join E_ROADWAYDETAIL e on b.UNIT = e.UNIT
//where b.UNIT in(select UNIT from B_UNIT where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID 
//                            in(select SEGID from B_INVNTORY where RDWAYID='{0}' and DIRECTION_DICTID={1})))
//and e.INSPECID=(Select top 1 a.INSPECID from E_ROADWAYDETAIL a where a.RDWAYID='{0}' and a.DIRECTION_DICTID={1}
//and LEFT(a.YEAR_M,4)=DATENAME(YYYY,GETDATE())
//order by a.YEAR_M desc)
//order by b.BEGINMP", rdid,dir);
                string sql = string.Format(@"select a.* from E_ROADWAYDETAIL a where not exists(select 1 
from E_ROADWAYDETAIL b where b.unit=a.unit and b.YEAR_M>a.YEAR_M)
and a.RDWAYID='{0}' and DIRECTION_DICTID={1} order by a.BEGINMP",rdid,dir);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    da.Fill(dt);
                    da.FillSchema(dt, SchemaType.Source);
                    return dt;
                }
            }
        }
    }
}
